package com.peenongsaban.freelance.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.peenongsaban.freelance.bean.AddressBean;
import com.peenongsaban.freelance.bean.AddressTypeBean;
import com.peenongsaban.freelance.bean.EducationBean;
import com.peenongsaban.freelance.bean.EducationLevelBean;
import com.peenongsaban.freelance.bean.ExperienceBean;
import com.peenongsaban.freelance.bean.JobBean;
import com.peenongsaban.freelance.bean.LanguageSkillBean;
import com.peenongsaban.freelance.bean.PaymentOfJobBean;
import com.peenongsaban.freelance.bean.PaymentOfServiceBean;
import com.peenongsaban.freelance.bean.PictureBean;
import com.peenongsaban.freelance.bean.PostJobBean;
import com.peenongsaban.freelance.bean.PostServiceBean;
import com.peenongsaban.freelance.bean.RegisterBean;
import com.peenongsaban.freelance.bean.RequestJobBean;
import com.peenongsaban.freelance.bean.ServiceBean;
import com.peenongsaban.freelance.bean.UserBean;
import com.peenongsaban.freelance.connection.DatabaseConnectionPool;
import com.peenongsaban.freelance.dao.FreelanceDetailDAO;
import com.peenongsaban.freelance.meta.AddressMeta;
import com.peenongsaban.freelance.meta.AddressTypeMeta;
import com.peenongsaban.freelance.meta.EducationLevelMeta;
import com.peenongsaban.freelance.meta.EducationMeta;
import com.peenongsaban.freelance.meta.ExperienceMeta;
import com.peenongsaban.freelance.meta.JobMeta;
import com.peenongsaban.freelance.meta.LanguageSkillMeta;
import com.peenongsaban.freelance.meta.PaymentOfJobMeta;
import com.peenongsaban.freelance.meta.PostJobMeta;
import com.peenongsaban.freelance.meta.PostServiceMeta;
import com.peenongsaban.freelance.meta.RegisterMeta;
import com.peenongsaban.freelance.meta.ServiceMeta;
import com.peenongsaban.freelance.meta.UserMeta;
import com.peenongsaban.freelance.model.FreelanceDetailModel;
import com.peenongsaban.freelance.utils.ExceptionUtil;
import com.peenongsaban.freelance.utils.StringUtils;

import freemarker.template.utility.StringUtil;

public class FreelanceDetailDAOImpl implements FreelanceDetailDAO{

	@Override
	public RegisterBean getFreelanceDetail(FreelanceDetailModel model) {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		RegisterBean register = new RegisterBean();
		PreparedStatement select_register_statement = null;
		PreparedStatement select_education_statement = null;
		PreparedStatement select_language_statement = null;
		PreparedStatement select_experience_statement = null;
		PreparedStatement select_address_statement = null;
		PreparedStatement select_requestjob_statement = null;
		PreparedStatement select_postservice_statement = null;
		PreparedStatement select_payment_statement = null;
		
		try {
			String select_register_sql = "";
			select_register_statement = conn.prepareStatement(select_register_sql);
			ResultSet rs_regiser = select_register_statement.executeQuery();
			
			if(rs_regiser.next()){
				register.setRegisterDate(rs_regiser.getDate(RegisterMeta.COL_REGISTER_DATE));  
				
				UserBean user = new UserBean();
				user.setName_th(rs_regiser.getString(UserMeta.COL_NAME_TH)); 
				user.setName_en(rs_regiser.getString(UserMeta.COL_NAME_EN));
				user.setLastName_th(rs_regiser.getString(UserMeta.COL_LASTNAME_TH));
				user.setLastName_en(rs_regiser.getString(UserMeta.COL_LASTNAME_EN));
				user.setBirthdate(rs_regiser.getDate(UserMeta.COL_BIRTHDATE));
				user.setGender(rs_regiser.getString(UserMeta.COL_GENDER));
				
				String phone = rs_regiser.getString(UserMeta.COL_PHONE);
				String[] phone_arr = null;
				if(StringUtils.isEmptyOrNull(phone)){
					phone_arr = phone.split(",");
				}
				user.setPhone(phone_arr);
				
				user.setFax(rs_regiser.getString(UserMeta.COL_FAX));
				
				String email = rs_regiser.getString(UserMeta.COL_EMAIL);
				String[] email_arr = null;
				if(StringUtils.isEmptyOrNull(email)){
					email_arr = email.split(",");
				}
				user.setEmail(email_arr);
				
				user.setFacebook(rs_regiser.getString(UserMeta.COL_FACEBOOK));
				user.setLine(rs_regiser.getString(UserMeta.COL_LINE));
				user.setTwitter(rs_regiser.getString(UserMeta.COL_TWITTER));
				
				String website = rs_regiser.getString(UserMeta.COL_WEBSITE);
				String[] website_arr = null;
				if(StringUtils.isEmptyOrNull(website)){
					website_arr = website.split(",");
				}
				user.setWebsite(website_arr);
				
				user.setPicture(rs_regiser.getString(UserMeta.COL_PICTURE));
				user.setUserStatus(rs_regiser.getString(UserMeta.COL_USER_STATUS));
				user.setIntroduce(rs_regiser.getString(UserMeta.COL_INTRODUCE));
				user.setAlias(rs_regiser.getString(UserMeta.COL_ALIAS));
				user.setTrustLevel(rs_regiser.getInt(UserMeta.COL_TRUST_LEVEL));
				
				Vector<EducationBean> listEducation = new Vector<EducationBean>();
				String select_education_sql = "";
				select_education_statement = conn.prepareStatement(select_education_sql);
				ResultSet rs_education = select_education_statement.executeQuery();
				
				while(rs_education.next()){
					EducationLevelBean educationLevel = new EducationLevelBean();
					educationLevel.setEducationLevelName(rs_education.getString(EducationLevelMeta.COL_EDUCATION_LEVEL_NAME)); 
					
					EducationBean education = new EducationBean();
					education.setEducationLevel(educationLevel);
					education.setMajor(rs_education.getString(EducationMeta.COL_MAJOR));
					education.setUniversity(rs_education.getString(EducationMeta.COL_UNIVERSITY));
					education.setProvince(rs_education.getString(EducationMeta.COL_PROVINCE));
					education.setStartYear(rs_education.getString(EducationMeta.COL_START_YEAR));
					education.setEndYear(rs_education.getString(EducationMeta.COL_END_YEAR));
					
					listEducation.add(education);
				}
				user.setListEducation(listEducation); 
				
				Vector<LanguageSkillBean> listLanguageSkill = new Vector<LanguageSkillBean>();
				String select_language_sql = "";
				select_language_statement = conn.prepareStatement(select_language_sql);
				ResultSet rs_language = select_language_statement.executeQuery();
				
				while(rs_language.next()){
					LanguageSkillBean language = new LanguageSkillBean();
					language.setLanguageName(rs_language.getString(LanguageSkillMeta.COL_LANGUAGE_NAME));
					language.setListeningLevel(rs_language.getInt(LanguageSkillMeta.COL_LISTENING_LEVEL));
					language.setSpeakingLevel(rs_language.getInt(LanguageSkillMeta.COL_SPEAKING_LEVEL));
					language.setReadingLevel(rs_language.getInt(LanguageSkillMeta.COL_READING_LEVEL));
					language.setWritingLevel(rs_language.getInt(LanguageSkillMeta.COL_WRITING_LEVEL));
					
					listLanguageSkill.add(language);
				}
				user.setListLanguageSkill(listLanguageSkill);
				
				Vector<ExperienceBean> listExperience = new Vector<ExperienceBean>();
				String select_experience_sql = "";
				select_experience_statement = conn.prepareStatement(select_experience_sql);
				ResultSet rs_experience = select_experience_statement.executeQuery();
				
				while(rs_experience.next()){
					ExperienceBean experience = new ExperienceBean();
					experience.setCompanyName(rs_experience.getString(ExperienceMeta.COL_COMPANY_NAME));
					experience.setProvince(rs_experience.getString(ExperienceMeta.COL_PROVINCE));
					experience.setPosition(rs_experience.getString(ExperienceMeta.COL_POSITION));
					experience.setSalary(rs_experience.getString(ExperienceMeta.COL_SALARY));
					experience.setStartYear(rs_experience.getString(ExperienceMeta.COL_START_YEAR));
					experience.setEndYear(rs_experience.getString(ExperienceMeta.COL_END_YEAR));
					
					listExperience.add(experience);
				}
				user.setListExperience(listExperience);
				
				Vector<AddressBean> listAddress = new Vector<AddressBean>();
				String select_address_sql = "";
				select_address_statement = conn.prepareStatement(select_address_sql);
				ResultSet rs_address = select_address_statement.executeQuery();
				
				while(rs_address.next()){
					AddressTypeBean addressType = new AddressTypeBean();
					addressType.setAddressTypeName(rs_address.getString(AddressTypeMeta.COL_ADDRESS_TYPE_NAME));
					
					AddressBean address = new AddressBean();
					address.setAddressType(addressType);
					address.setBuilding(rs_address.getString(AddressMeta.COL_BUILDING));
					address.setRoom(rs_address.getString(AddressMeta.COL_ROOM));
					address.setAddress(rs_address.getString(AddressMeta.COL_ADDRESS));
					address.setVillage(rs_address.getString(AddressMeta.COL_VILLAGE));
					address.setAlley(rs_address.getString(AddressMeta.COL_ALLEY));
					address.setRoad(rs_address.getString(AddressMeta.COL_ROAD));
					address.setSubdistrict(rs_address.getString(AddressMeta.COL_SUBDISTRICT));
					address.setDistrict(rs_address.getString(AddressMeta.COL_DISTRICT));
					address.setProvince(rs_address.getString(AddressMeta.COL_PROVINCE));
					address.setPostcode(rs_address.getString(AddressMeta.COL_POSTCODE));
					
					listAddress.add(address);
				}
				user.setListAddress(listAddress); 
				
				user.setExperienceOther(rs_regiser.getString(UserMeta.COL_EXPERIENCE_OTHER));
				
				Vector<RequestJobBean> listRequestJob = new Vector<RequestJobBean>();
				String select_requestjob_sql = "";
				select_requestjob_statement = conn.prepareStatement(select_requestjob_sql);
				ResultSet rs_requestjob = select_requestjob_statement.executeQuery();
				
				while(rs_requestjob.next()){
					RequestJobBean requestjob = new RequestJobBean();
										
					JobBean job = new JobBean();
					job.setJobID(rs_requestjob.getString(JobMeta.COL_JOB_ID));
					job.setJobName(rs_requestjob.getString(JobMeta.COL_JOB_NAME));
					job.setDescriptionOfJob(rs_requestjob.getString(JobMeta.COL_DESCRIPTION_OF_JOB));
					
					PaymentOfJobBean payment = new PaymentOfJobBean();
					payment.setFixPrice(rs_requestjob.getDouble(PaymentOfJobMeta.COL_FIX_PRICE));
					payment.setMinPrice(rs_requestjob.getDouble(PaymentOfJobMeta.COL_MIN_PRICE));
					payment.setMaxPrice(rs_requestjob.getDouble(PaymentOfJobMeta.COL_MAX_PRICE));
					
					job.setPaymentOfJob(payment); 
					
					PostJobBean postJob = new PostJobBean();
					postJob.setJob(job);
					
					requestjob.setPostJob(postJob); 
					
					listRequestJob.add(requestjob);
				}
				user.setListRequestJob(listRequestJob);
				
				Vector<PostServiceBean> listPostService = new Vector<PostServiceBean>();
				String select_postservice_sql = "";
				select_postservice_statement = conn.prepareStatement(select_postservice_sql);
				ResultSet rs_postservice = select_postservice_statement.executeQuery();
				
				while(rs_postservice.next()){
					PostServiceBean postService = new PostServiceBean();
					postService.setAmountRequestPost(rs_postservice.getInt(PostServiceMeta.COL_AMOUNT_REQUEST_POST));
					postService.setStatusOfPost(rs_postservice.getString(PostServiceMeta.COL_STATUS_OF_POST));
					
					ServiceBean service = new ServiceBean();
					service.setServiceID(rs_postservice.getString(ServiceMeta.COL_SERVICE_ID));
					service.setServiceName(rs_postservice.getString(ServiceMeta.COL_SERVICE_NAME));
					service.setDescriptionOfService(rs_postservice.getString(ServiceMeta.COL_DESCRIPTION_OF_SERVICE));
					service.setPriceRange(rs_postservice.getString(ServiceMeta.COL_PRICE_RANGE));
					
					postService.setService(service); 
					
					listPostService.add(postService);
				}
				user.setListPostService(listPostService);
			}			
			
			return register;
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);		
		} finally {
			try {
				if(select_aptitude_type_statement != null)				
					select_aptitude_type_statement.close();
				if(select_aptitude_statement != null)	
					select_aptitude_statement.close();
				if(select_portfolio_statement != null)	
					select_portfolio_statement.close();
				if(select_portfolio_user_statement != null)	
					select_portfolio_user_statement.close();
				
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		
		return null;
	}
	
}
